using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using System.Xml;

namespace Customs.Components.SXXK.ThanhKhoan
{   
    /// <summary>
    /// chua cac phuong thuc de lay thong tin luong npl tai xuat, 
    /// ton dau va ton cuoi cua tung to khai sau tung lan thanh ly
    /// cua tung to khai xuat theo san pham
    /// </summary>
    public class ThongTinTonNPLXuat
    {
        public static DataSet GetTonNPLXuatOfLanThanhLy(string maHaiQuan, string maDoanhNghiep, int lanthanhly)
        {
            string connectionString = ConnectionManager.GetConnectionString("SXXK", maHaiQuan);
            string query = "SELECT     dbo.DNPLXUAT_TL.LAN_TL, dbo.DNPLXUAT_TL.MA_DV, dbo.DNPLXUAT_TL.SOTKX, dbo.DNPLXUAT_TL.MA_HQ, dbo.DNPLXUAT_TL.MA_LHX,  " +
                           "dbo.DNPLXUAT_TL.NAMDKX, dbo.DNPLXUAT_TL.MA_HQX, dbo.DNPLXUAT_TL.MA_SP, dbo.DNPLXUAT_TL.LUONG, dbo.DNPLXUAT_TL.MA_NPL,  " +
                           "dbo.DNPLXUAT_TL.TON_DAU_NPL, dbo.DNPLXUAT_TL.TON_NPL, dbo.DNPLXUAT.LUONG AS LuongSanPham, dbo.DNPLXUAT.LUONG_NPL, " +
                           "dbo.DNPLXUAT.TON_NPL AS TonNPLXuat " +
                           "FROM         dbo.DNPLXUAT_TL INNER JOIN " +
                           "dbo.DNPLXUAT ON dbo.DNPLXUAT_TL.SOTKX = dbo.DNPLXUAT.SOTK AND dbo.DNPLXUAT_TL.MA_LHX = dbo.DNPLXUAT.MA_LH AND " +
                           "dbo.DNPLXUAT_TL.NAMDKX = dbo.DNPLXUAT.NAMDK AND dbo.DNPLXUAT_TL.MA_HQX = dbo.DNPLXUAT.MA_HQ AND " +
                           "dbo.DNPLXUAT_TL.MA_SP = dbo.DNPLXUAT.MA_SP AND dbo.DNPLXUAT_TL.MA_NPL = dbo.DNPLXUAT.MA_NPL " +
                            "where dbo.DNPLXUAT_TL.LAN_TL=@LAN_TL and dbo.DNPLXUAT_TL.MA_DV=@Ma_DV and dbo.DNPLXUAT_TL.MA_HQ=@Ma_HQ";

            SqlParameter[] parameters = new SqlParameter[3];

            parameters[0] = new SqlParameter("@Ma_HQ", SqlDbType.Char);
            parameters[0].Size = 6;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@Ma_DV", SqlDbType.VarChar);
            parameters[1].Size = 14;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maDoanhNghiep;

            parameters[2] = new SqlParameter("@LAN_TL", SqlDbType.Int);
            parameters[2].Direction = ParameterDirection.Input;
            parameters[2].Value = lanthanhly;

            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query, parameters);
            return ds;
        }
        public static DataSet GetTonNPLXuat(string maHaiQuan, string maDoanhNghiep)
        {
            string connectionString = ConnectionManager.GetConnectionString("SXXK", maHaiQuan);
            string query = "SELECT     dbo.DNPLXUAT_TL.LAN_TL, dbo.DNPLXUAT_TL.MA_DV, dbo.DNPLXUAT_TL.SOTKX, dbo.DNPLXUAT_TL.MA_HQ, dbo.DNPLXUAT_TL.MA_LHX,  "+
                           "dbo.DNPLXUAT_TL.NAMDKX, dbo.DNPLXUAT_TL.MA_HQX, dbo.DNPLXUAT_TL.MA_SP, dbo.DNPLXUAT_TL.LUONG, dbo.DNPLXUAT_TL.MA_NPL,  "+
                           "dbo.DNPLXUAT_TL.TON_DAU_NPL, dbo.DNPLXUAT_TL.TON_NPL, dbo.DNPLXUAT.LUONG AS LuongSanPham, dbo.DNPLXUAT.LUONG_NPL, "+
                           "dbo.DNPLXUAT.TON_NPL AS TonNPLXuat "+
                           "FROM         dbo.DNPLXUAT_TL INNER JOIN "+
                           "dbo.DNPLXUAT ON dbo.DNPLXUAT_TL.SOTKX = dbo.DNPLXUAT.SOTK AND dbo.DNPLXUAT_TL.MA_LHX = dbo.DNPLXUAT.MA_LH AND "+
                           "dbo.DNPLXUAT_TL.NAMDKX = dbo.DNPLXUAT.NAMDK AND dbo.DNPLXUAT_TL.MA_HQX = dbo.DNPLXUAT.MA_HQ AND "+
                           "dbo.DNPLXUAT_TL.MA_SP = dbo.DNPLXUAT.MA_SP AND dbo.DNPLXUAT_TL.MA_NPL = dbo.DNPLXUAT.MA_NPL "+
                            "where dbo.DNPLXUAT_TL.MA_DV=@Ma_DV and dbo.DNPLXUAT_TL.MA_HQ=@Ma_HQ";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@Ma_HQ", SqlDbType.Char);
            parameters[0].Size = 6;
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@Ma_DV", SqlDbType.VarChar);
            parameters[1].Size = 14;
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = maDoanhNghiep;

            DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query, parameters);
            return ds;
        }
    }
}
